using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;
using System.Windows.Forms;

namespace SQLitenSupport.Data
{

    /// <summary>
    /// 
    /// </summary>
    public class SQLTransactions : IDisposable
    {

        #region Events & Objects

        // SQLite Connectors
        private SQLiteConnection sConn;
        private SQLiteCommand sComm;
        private SQLiteDataAdapter sData;
        private SQLiteDataReader sReader;

        #endregion

        #region Class Constants, Variables & Arrays

        // Disposed flag
        private bool _bDisposed;

        // Backing variables for connection and query strings
        private string _ConnectionString, _QueryString;

        #endregion

        #region Properties

        /// <summary>
        /// Gets/sets the Connection String
        /// </summary>
        public string ConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        }

        /// <summary>
        /// Gets/sets the Query String
        /// </summary>
        public string QueryString
        {
            get { return _QueryString; }
            set { _QueryString = value; }
        }

        #endregion

        #region Constructor(s)

        /// <summary>
        /// Initializes a new instance of the SQLTransactions class
        /// </summary>
        public SQLTransactions()
        {

            // Set disposed flag to false
            _bDisposed = false;

        }

        /// <summary>
        /// Initializes a new instance of the SQLTransactions class
        /// </summary>
        /// <param name="connectionstring">Parameters used to connect to the SQLite database</param>
        /// <param name="querystring">The query statement to be executed on the database</param>
        public SQLTransactions( string connectionstring, string querystring )
        {

            // Set disposed flag to false
            _bDisposed = false;

            // Set connection & query strings
            this.ConnectionString = connectionstring;
            this.QueryString = querystring;

        }

        #endregion

        #region Database Manipulation Method(s)

        // Method to create a blank database
        public SQLStatus CreateDatabase( string DatabaseName )
        {

            try
            {

                // Create a blank file
                SQLiteConnection.CreateFile( DatabaseName );

                // Return Success
                return SQLStatus.CreateDatabaseSuccess;

            } // try
            catch (SQLiteException ex)
            {

                // Keep compiler happy
                ex.Message.ToString();

                // Return failure
                return SQLStatus.CreateDatabaseFailure;

            } // catch

        }

        #endregion

        #region Schema Related Method(s)

        /// <summary>
        /// Fetches the list of tables in a SQLite Database
        /// </summary>
        /// <returns>A list of the tables as a DataTable</returns>
        public DataTable GetTables()
        {

            // Local vars
            DataTable resultTable = null;

            // Create connectors
            sConn = new SQLiteConnection( _ConnectionString );

            try
            {

                // Open connection
                sConn.Open();

                // Fetch table names
                resultTable = sConn.GetSchema( "Tables" );

                // Close connection
                sConn.Close();

            } // try
            catch (SQLiteException ex)
            {

                // Keep compiler happy
                ex.Message.ToString();

                // Throw exception
                throw new SQLException( SQLStatus.TableListGetFailure );


            } // catch
            finally
            {

                // Dispose the connectors
                DisposeConnectors();

            }

            // Return results
            return resultTable;

        }

        /// <summary>
        /// Fetches the Schema for a Table in a SQLite Database
        /// </summary>
        /// <param name="TableName">Name of the Table</param>
        /// <returns>The Table Schema as a DataTable</returns>
        public DataSet GetSchema( string tableName )
        {

            // Local vars
            DataSet resultSet = new DataSet();
            string[] tableInfo = new string[] { null, null, tableName, null };

            // Create connectors
            sConn = new SQLiteConnection( _ConnectionString );

            try
            {

                // Open connection
                sConn.Open();

                // Fetch Schema
                resultSet.Tables.Add( sConn.GetSchema( "Columns", tableInfo ) );
                resultSet.Tables.Add( sConn.GetSchema( "Indexes", tableInfo ) );
                resultSet.Tables.Add( sConn.GetSchema( "IndexColumns", tableInfo ) );
                resultSet.Tables.Add( sConn.GetSchema( "ForeignKeys", tableInfo ) );

                // Close the connection
                sConn.Close();

            } // try
            catch (SQLiteException ex)
            {

                // Keep compiler happy
                ex.Message.ToString();

                // Throw error
                throw new SQLException( SQLStatus.TableSchemaGetFailure );

            } // catch
            finally
            {

                // Dispose the connectors
                DisposeConnectors();

            } // finally

            // Return result
            return resultSet;

        }

        #endregion

        #region Table Manipulation Methods

        // Method to create a table
        public SQLStatus CreateTable( DataTable Schema, string tableName )
        {

            // Local vars
            bool bAns = false;

            // If not blank table
            if (Schema.Rows.Count != 0)
            {
                // Build the query string
                _QueryString = "CREATE TABLE " + tableName + " ( ";

                // Loop and add the Fields
                for (int count = 0; count < Schema.Rows.Count; count++)
                {

                    // Add a comma if not last row
                    if (count != 0)
                        _QueryString += ", ";

                    // Add field name
                    _QueryString += (string)Schema.Rows[count]["Field Name"];

                    // Add data type
                    _QueryString += " " + (string)Schema.Rows[count]["Data Type"];

                    // Add default value
                    _QueryString += ((string)Schema.Rows[count]["Default"] != "") ?
                        " DEFAULT " + Schema.Rows[count]["Default"] : "";

                    // Check and indicate primary key
                    _QueryString += (Schema.Rows[count]["Primary Key"].Equals( true )) ? " PRIMARY KEY" : "";

                } // end for

                // Terminate query string
                _QueryString += " );";

                MessageBox.Show( _QueryString );

                // Create connectors
                sConn = new SQLiteConnection( _ConnectionString );
                sComm = new SQLiteCommand( _QueryString, sConn );

                try
                {

                    // Open connection
                    sConn.Open();

                    // Execute statement
                    sComm.ExecuteNonQuery();

                    // Close connection
                    sConn.Close();

                    // Return flag
                    bAns = true;

                }
                catch (SQLiteException ex)
                {

                    // Keep compiler happy
                    ex.Message.ToString();

                    // Return flag
                    bAns = false;

                } // end try-catch
                finally
                {

                    // Dispose all connectors used
                    DisposeConnectors();

                }

                return bAns ? SQLStatus.CreateTableSuccess : SQLStatus.CreateTableFailure;

            } // end if

            // Empty data - return failure
            return SQLStatus.CreateTableFailure;

        }

        #endregion

        #region Miscellaneous Helper Methods

        // Method to extract filename from full path
        private string extractFileName( string fullPath )
        {

            // Extract just filename from full path
            int count = fullPath.Length;
            while (fullPath[count - 1] != '\\')
                count--;

            // Return result
            return fullPath.Substring( count );

        }

        #endregion

        #region IDisposable Members

        // Dispose method
        public void Dispose()
        {

            // If not disposed already
            if (!Disposed())
            {

                // Set disposed to true
                _bDisposed = true;

                // Call dispose routines
                DisposeConnectors();

            } // if

        }

        // Checking method to see if already disposed
        // so that Dispose() isn't called twice
        public bool Disposed()
        {

            // Return result
            return _bDisposed;

        }

        #endregion

        #region Destructor(s)

        // Method to dispose all connector objects
        private void DisposeConnectors()
        {

            // Dispose and set objects to null
            if (sConn != null)
            {
                sConn.Dispose(); sConn = null;
            }
            if (sComm != null)
            {
                sComm.Dispose(); sComm = null;
            }
            if (sData != null)
            {
                sData.Dispose(); sData = null;
            }

        }

        #endregion

    } // class

} // namespace
